package com.arvato.common.util;

import java.io.File;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

import com.arvato.common.config_bean.FilehomeConfig;
import com.arvato.wxwork.bean.bean_do.staff.WxworkStaffInfoDo;
import com.arvato.wxwork.bean.bean_vo.member.WxworkExternalInfoVo;
import com.arvato.wxwork.bean.bean_vo.staff.WxworkStaffInfoVo;

import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * 通过POI解析Excle的工具类，这个类解析Excle返回一个以表中列为单位的集合,或者把集合转成Excel
 * 读取都是用的POI，对xls和xlsx支持比较好。 写入都是用的jxl，写入的内容比较完整。 org.apache.poi.xssf
 * 这个包要POI3.9版本才行
 * 
 * @author smallking
 */
public class ExcelUtil {
	/**
	 * 解析excel员工表，返回表里员工集合
	 * @param file
	 * @return
	 */
	public static List<WxworkStaffInfoDo> readStaffExcel(File file) {
		List<WxworkStaffInfoDo> staffList = new ArrayList<WxworkStaffInfoDo>();
		try {
			InputStream inputStream = new FileInputStream(file);
			// 将文件的输入流转换成Workbook
			Workbook wb = WorkbookFactory.create(inputStream);
			// 获得第一个工作表sheet页
			Sheet sheet = wb.getSheetAt(0);
			int rowNum = sheet.getLastRowNum() + 1;
			for (int i = 1; i < rowNum; i++) {
				WxworkStaffInfoDo staffDo = new WxworkStaffInfoDo();
				// 获得Excel某一行数据
				Row row = sheet.getRow(i);
				if(row == null) {
					continue;
				}
				short cellNum = row.getLastCellNum();
				for (int j = 0; j < cellNum; j++) {
					// 获得Excel某一行下某一个单元格的数据
					Cell cell = row.getCell(j);
					if (cell != null) {
						cell.setCellType(Cell.CELL_TYPE_STRING);
						String value = cell.getStringCellValue();
						System.out.println(i+ "行  " + j + "列" + "值:" + value);
						// 第一列是员工编号
						if (j == 0) {
							staffDo.setStaffCode(value);
						}
						// 第二列是员工姓名
						if (j == 1) {
							staffDo.setName(value);
						}
						// 第三列，第四列是岗位
						if (j == 2) {
							staffDo.setPosition(value);
						}
						if(j == 3) {
							if(value != null && !value.trim().equals("")) {
								staffDo.setPosition(staffDo.getPosition() + "(" + value + ")");
							}
						}
						// 第五列，第六列 部门编码，部门名称 但是我们以科室名称和科室代码为准
						if(j == 4 || j == 5) {
							continue;
						}
						// 第七列是科室代码
						if(j == 6) {
							staffDo.setDepartmentCode(value);
						}
						// 第八列是科室名称
						if(j == 7) {
							staffDo.setDepartmentName(value);
						}
						// 第九列是领导的员工号
						if (j == 8) {
							staffDo.setLeaderCode(value);
						}
						// 第十列是领导的姓名
						if(j == 9) {
							staffDo.setLeaderName(value);
						}
						// 第十一列是门店的代码
						if(j == 10) {
							staffDo.setStoreCode(value);
						}
						// 第十二列是是否可见小程序的权限
						if(j == 11) {
							if("0".equals(value) || "1".equals(value)) {
								staffDo.setVisible(Integer.valueOf(value));
							}else {
								staffDo.setVisible(0);
							}
						}
					}
				}
				staffList.add(staffDo);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		}
		return staffList;
	}

	/**
	 * 根据所给的员工list，生成Excel文件 返回生成的Excel文件名
	 * @param staffList
	 * @return
	 * @throws IOException
	 */
	public static String writeStaffExcle(List<WxworkStaffInfoVo> staffList)
			throws IOException, RowsExceededException, WriteException {
		String fileName =  FilehomeConfig.FILEHOME + System.currentTimeMillis() + ".xls";
		FileOutputStream output = new FileOutputStream(fileName);
		// 创建Excel
		WritableWorkbook workbook = jxl.Workbook.createWorkbook(output);
		// 创建sheet 设置单元格列宽
		WritableSheet sheet = workbook.createSheet("sheet", 0);
		sheet.setColumnView(0, 5);
		sheet.setColumnView(1, 15);
		sheet.setColumnView(2, 10);
		sheet.setColumnView(3, 10);
		sheet.setColumnView(4, 10);
		sheet.setColumnView(5, 10);
		sheet.setColumnView(6, 10);
		sheet.setColumnView(7, 50);
		sheet.setColumnView(8, 20);
		// 先生成表头
		Label label1 = new Label(0, 0, "NO.");
		Label label2 = new Label(1, 0, "分公司");
		Label label3 = new Label(2, 0, "所属长");
		Label label4 = new Label(3, 0, "门店编号");
		Label label5 = new Label(4, 0, "门店简称");
		Label label6 = new Label(5, 0, "BC员工号");
		Label label7 = new Label(6, 0, "BC姓名");
		Label label8 = new Label(7, 0, "客户昵称+Openid");
		Label label9 = new Label(8, 0, "添加日期");
		sheet.addCell(label1);
		sheet.addCell(label2);
		sheet.addCell(label3);
		sheet.addCell(label4);
		sheet.addCell(label5);
		sheet.addCell(label6);
		sheet.addCell(label7);
		sheet.addCell(label8);
		sheet.addCell(label9);
		int rowCount = 1;
		for (int i = 0; i < staffList.size(); i++) {
			WxworkStaffInfoVo staff = staffList.get(i);
			List<WxworkExternalInfoVo> externalList = staff.getExternalList();
			if(externalList.size() == 0) {
				Label bodyLabel1 = new Label(0, rowCount, rowCount + "");
				Label bodyLabel2 = new Label(1, rowCount, staff.getDepartmentName());
				Label bodyLabel3 = new Label(2, rowCount, staff.getLeaderName());
				Label bodyLabel4 = new Label(3, rowCount, staff.getStoreCode());
				Label bodyLabel5 = new Label(4, rowCount, staff.getShortName());
				Label bodyLabel6 = new Label(5, rowCount, staff.getStaffCode());
				Label bodyLabel7 = new Label(6, rowCount, staff.getName());
				Label bodyLabel8 = new Label(7, rowCount, "");
				Label bodyLabel9 = new Label(8, rowCount, "----");
				sheet.addCell(bodyLabel1);
				sheet.addCell(bodyLabel2);
				sheet.addCell(bodyLabel3);
				sheet.addCell(bodyLabel4);
				sheet.addCell(bodyLabel5);
				sheet.addCell(bodyLabel6);
				sheet.addCell(bodyLabel7);
				sheet.addCell(bodyLabel8);
				sheet.addCell(bodyLabel9);
				rowCount ++;
			}else {
				for(int j = 0; j < externalList.size(); j ++) {
					WxworkExternalInfoVo externalInfo = externalList.get(j);
					Label bodyLabel1 = new Label(0, rowCount, rowCount + "");
					Label bodyLabel2 = new Label(1, rowCount, staff.getDepartmentName());
					Label bodyLabel3 = new Label(2, rowCount, staff.getLeaderName());
					Label bodyLabel4 = new Label(3, rowCount, staff.getStoreCode());
					Label bodyLabel5 = new Label(4, rowCount, staff.getShortName());
					Label bodyLabel6 = new Label(5, rowCount, staff.getStaffCode());
					Label bodyLabel7 = new Label(6, rowCount, staff.getName());
					Label bodyLabel8 = new Label(7, rowCount, externalInfo.getName() + "(" + externalInfo.getWxExternalUserid() + ")");
					Label bodyLabel9 = new Label(8, rowCount, externalInfo.getBindTime());
					sheet.addCell(bodyLabel1);
					sheet.addCell(bodyLabel2);
					sheet.addCell(bodyLabel3);
					sheet.addCell(bodyLabel4);
					sheet.addCell(bodyLabel5);
					sheet.addCell(bodyLabel6);
					sheet.addCell(bodyLabel7);
					sheet.addCell(bodyLabel8);
					sheet.addCell(bodyLabel9);
					rowCount ++;
				}
			}
		}
		workbook.write();
		workbook.close();
		output.close();
		return fileName;
	}

}
